import logging
import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from qmt_tool.doris_tool import DbTool
from qmt_tool.redis_cache import RedisQueue
from qmt_data import jsl_data
import akshare as ak
import time
import pandas as pd
from sqlalchemy import text
from qmt_tool.MyTT import REF, SMA, EMA, BARSLAST, LLV, HHV
from qmt_tool.confile_tool import get_config_jsl_file
from datetime import datetime, timedelta
import pendulum
import json
import hashlib


class OrganizeData():
    def __init__(self):
        self.db_tool = DbTool()
        self.engine = self.db_tool.get_dbengine_quant()
        self.engine_result = self.db_tool.get_dbengine_quant_result()
        # self.logcmd = get_logger(name='downdata2db')
        self.redis = RedisQueue()

    def download_em_gn_todb(self):
        # 将东财的概念 写入db
        gainian_df_temp = ak.stock_board_concept_name_em()
        drop_list = "融资融券|创业板综|次新股|盈利预增|预盈预增|预亏预减|富时罗素|国企改革|注册制次新股|沪股通|标准普尔|" + \
                    "广东板块|江苏板块|浙江板块|昨日涨停|昨日涨停_含一|北京板块|专精特新|上海板块|长江三角|一带一路|参股银行|上证180_|" + \
                    "昨日连板|昨日连板_含一|参股新三板|河南板块|贬值受益|深证100R|工业4.0|成渝特区|陕西板块|黑龙江|河北板块|2025规划|" + \
                    "核算采样亭|杭州亚运会|重庆板块|口罩|PPP模式|四川板块|QFII重仓|山东板块|机构重仓|深股通|中证500|深成500|参股券商|昨日触板|" + \
                    "HS300_|雄安新区|安徽板块|江西板块|北京冬奥|海南板块|山西板块|吉林板块|上证380|基金重仓|沪企改革|甘肃板块|" + \
                    "举牌|青海板块|B股|云南板块|时空大数据|贵州板块|京津冀|MSCI中国|央企改革"

        data_todb3 = gainian_df_temp[gainian_df_temp["板块名称"].str.contains(drop_list) == False]
        data_df = data_todb3[['板块名称', '板块代码', '上涨家数', '下跌家数']].copy()
        data_df['gn_stock_num'] = data_df['上涨家数'] + data_df['下跌家数']

        data_df_2 = data_df[['板块名称', '板块代码', 'gn_stock_num']]
        data_df_2.columns = ['em_gn_nm', 'em_gn_code', 'gn_stock_num']
        self.db_tool.save_to_mysql_append(data_df_2, self.engine, "ak_gainian_list_em")

    def get_em_notexist_gn_stock_fromdb(self):
        sql_comm = "select * from  quant.ak_gainian_list_em where em_gn_nm not in (select distinct(gn_nm) from quant.ak_gainian_chengfen_em )  and em_gn_nm not like '昨日%'  "
        try:
            data_df = pd.read_sql(text(sql_comm), self.engine)
            return data_df
        except Exception as e:
            print('读取数据出错', e)
            # 设定需要获取数据的股票池
            data_df = pd.DataFrame()
            return data_df

    def download_em_gainian_chengfen(self):
        # 从东方财富获取概念成分股
        em_gainian_df_temp = self.get_em_notexist_gn_stock_fromdb()
        for dc_gn_i in em_gainian_df_temp.itertuples():
            name = dc_gn_i.em_gn_nm
            code = dc_gn_i.em_gn_code
            try:
                dc_gn_cf_df_tmp = ak.stock_board_concept_cons_em(symbol=name)

                dc_gn_chengfen_temp = pd.DataFrame(columns=['symbol', 'name', 'gn_nm', 'gn_code'])
                dc_gn_chengfen_temp['symbol'] = dc_gn_cf_df_tmp['代码']
                dc_gn_chengfen_temp['name'] = dc_gn_cf_df_tmp['名称']
                dc_gn_chengfen_temp['gn_nm'] = name
                dc_gn_chengfen_temp['gn_code'] = code
                self.db_tool.save_to_mysql_append(dc_gn_chengfen_temp, self.engine, "ak_gainian_chengfen_em")

            except Exception as e:
                print("这个东财概念股票成分失败", name)

    def get_stock_pd_fromdb(self):
        sql_comm = "SELECT symbol,name FROM ak_stock_list "
        try:
            data_df = pd.read_sql(text(sql_comm), self.engine)
            return data_df
        except Exception as e:
            print('读取数据出错', e)
            data_df = pd.DataFrame()
            return data_df
        # 设定需要获取数据的股票池



    def get_em_gn_fromdb(self):
        sql_comm = "select * from quant.ak_gainian_list_em where em_gn_nm not like '昨日%' "
        try:
            df = pd.read_sql(text(sql_comm), self.engine)
            return df
        except Exception as e:
            print('读取数据出错', e)

    def org_em_gn_in_one_stock(self):
        #从数据库中提取 概念和成分,并组织成1行
        sql_comm = 'SELECT * FROM ak_gainian_chengfen_em'
        try:
            data_df = pd.read_sql(text(sql_comm), self.engine)
        except Exception as e:
            print('读取数据出错', e)
            # 设定需要获取数据的股票池
            data_df = pd.DataFrame()
        stock_all_pd = data_df  # 获取股票李彪
        if len(stock_all_pd) < 4000:
            logging.error("没有股票list,请查看")
        dc_gn_df =data_df # 获取东财
        ##########概念列表
        stock_gainian_df = pd.DataFrame(columns=['symbol', 'name', 'em_gn_nm', 'ths_gn_nm'])
        for stock_i in stock_all_pd.itertuples():
            code = stock_i.symbol
            name = stock_i.name
            dc_gn_df_i = dc_gn_df[(dc_gn_df['symbol'] == code)]
            if  len(dc_gn_df_i) > 0:
                temp = {}
                temp['symbol'] = code
                temp['name'] = name

                if len(dc_gn_df_i) > 0:
                    dc_gn_name = ''
                    for dd in dc_gn_df_i.itertuples():
                        dc_gn_name = dd.gn_nm + "|" + dc_gn_name
                    temp['em_gn_nm'] = dc_gn_name
                else:
                    pass
                temp_df = pd.DataFrame(temp, index=[0])

                stock_gainian_df = pd.concat([stock_gainian_df, temp_df], ignore_index=True)
            else:
                pass
        # stock_gainian_df.dropna(axis=0, subset=['ths_gn_nm'], inplace=True)
        self.db_tool.save_to_mysql_append(stock_gainian_df, self.engine, "my_stock_gainian")

    def download_em_hy_todb(self):
        gainian_df_temp = ak.stock_board_industry_name_em()
        data_df = gainian_df_temp[['板块名称', '板块代码', '上涨家数', '下跌家数']].copy()
        data_df['gn_stock_num'] = data_df['上涨家数'] + data_df['下跌家数']
        data_df_2 = data_df[['板块名称', '板块代码', 'gn_stock_num']]
        data_df_2.columns = ['em_hy_nm', 'em_hy_code', 'hy_stock_num']
        self.db_tool.save_to_mysql_append(data_df_2, self.engine, "ak_hangye_list_em")

    def download_hangye_chengfen(self):
        dc_hangye_df = pd.DataFrame(columns=['symbol', 'name', 'hy_nm', 'hy_code'])
        dc_hangye_df_temp = ak.stock_board_industry_name_em()

        for dc_hangye_i in dc_hangye_df_temp.itertuples():
            print(dc_hangye_i.板块名称)
            name = dc_hangye_i.板块名称
            code = dc_hangye_i.板块代码
            try:
                dc_hy_cf_df_tmp = ak.stock_board_industry_cons_em(symbol=name)
                # print(ths_hy_cf_df_tmp)
                dc_hangye_chengfen_temp = pd.DataFrame(columns=['symbol', 'name', 'hy_nm', 'hy_code'])
                dc_hangye_chengfen_temp['symbol'] = dc_hy_cf_df_tmp['代码']
                dc_hangye_chengfen_temp['name'] = dc_hy_cf_df_tmp['名称']
                dc_hangye_chengfen_temp['hy_nm'] = name
                dc_hangye_chengfen_temp['hy_code'] = code
                dc_hangye_df = pd.concat([dc_hangye_df, dc_hangye_chengfen_temp])

            except Exception as e:
                print("这个东财行业数据获取失败了", name)

        self.db_tool.save_to_mysql_append(dc_hangye_df, self.engine, "ak_hangye_chengfen_em")


    ########### 东方财富行业信息################
    def org_em_hangye_in_one_stock(self):
        #获取行业成分股
        sql_comm = 'SELECT * FROM `ak_hangye_chengfen_em`'
        try:
            data_df = pd.read_sql(text(sql_comm), self.engine)
        except Exception as e:
            print('读取数据出错', e)
            # 设定需要获取数据的股票池
            data_df = pd.DataFrame()

        ##########一般不需要经常变动， 想起来变更一次就可以，只能白天跑
        stock_hangye_df = pd.DataFrame(columns=['symbol', 'name', 'em_hy_nm', 'ths_hy_nm'])
        # 获取股票列表数据

        stock_all_pd = self.get_stock_pd_fromdb()

        if len(stock_all_pd) < 4000:
            logging.error("没有股票list,请查看")

        dc_hangye_df =data_df  # 获取股票


        for stock_i in stock_all_pd.itertuples():
            code = stock_i.symbol
            name = stock_i.name

            dc_hangye_df_i = dc_hangye_df[(dc_hangye_df['symbol'] == code)]
            if  len(dc_hangye_df_i) > 0:
                temp = {}
                temp['symbol'] = code
                temp['name'] = name

                if len(dc_hangye_df_i) > 0:
                    dc_hy_name = ''
                    for dd in dc_hangye_df_i.itertuples():
                        dc_hy_name = dd.hy_nm + "|" + dc_hy_name
                    temp['em_hy_nm'] = dc_hy_name
                else:
                    pass
                temp_df = pd.DataFrame(temp, index=[0])
                stock_hangye_df = pd.concat([stock_hangye_df, temp_df])
            else:
                pass
        # stock_hangye_df.dropna(axis=0,subset=['ths_hy_nm'],inplace=True)
        self.db_tool.save_to_mysql_append(stock_hangye_df, self.engine, "my_stock_hangye")

    def orgnize_stock_hangye_gainian(self,):
        self.download_em_gn_todb()
        self.download_em_hy_todb()
        self.download_hangye_chengfen()
        self.download_em_gainian_chengfen()
        self.org_em_gn_in_one_stock()
        self.org_em_hangye_in_one_stock()
        sql_comm=f"""
        insert into my_stock_hangye_gainian(
symbol,
name,
em_gn_nm,
em_hy_nm,
ths_gn_nm,
ths_hy_nm,
etl_time
)
select 
hy.symbol,
hy.name,
gn.em_gn_nm,
hy.em_hy_nm,
gn.ths_gn_nm,
hy.ths_hy_nm,
now() as etl_time
from my_stock_hangye as hy, my_stock_gainian as gn where  gn.symbol=hy.symbol and hy.em_hy_nm is not null and gn.em_gn_nm is not null;
"""
        with self.engine.connect() as conn:
                conn.execute(text(sql_comm))
                conn.commit()



if __name__ == '__main__':
    orgnize2db = OrganizeData()
    # download2db.down_trade_cal()


    orgnize2db.orgnize_stock_hangye_gainian()